﻿using zijian666.Data.SqlElements;

namespace zijian666.Data;
public static partial class DbConnectionExtensions
{

    /// <summary>
    /// 获取分页查询 <seealso cref="IDbCommand"/> 对象
    /// </summary>
    /// <exception cref="ArgumentNullException"></exception>
    public static IDbCommand PagingCommand(this IDbConnection conn, FormattableString sql, long beginRowIndex, int rowsCount)
    {
        ArgumentNullException.ThrowIfNull(conn);
        ArgumentNullException.ThrowIfNull(sql);
        if (beginRowIndex < 0)
        {
            throw new ArgumentOutOfRangeException(nameof(beginRowIndex));
        }
        if (rowsCount <= 0)
        {
            throw new ArgumentOutOfRangeException(nameof(rowsCount));
        }

        return conn.CreateCommand(sql, null, cmd => cmd.CommandText = conn.GetSqlBuilder().PagingSql(cmd.CommandText, beginRowIndex, rowsCount));
    }

    /// <summary>
    /// 获取分页查询 <seealso cref="IDbCommand"/> 对象
    /// </summary>
    /// <exception cref="ArgumentNullException"></exception>
    public static IDbCommand PagingCommand<T>(this IDbConnection conn, ISqlSearcher<T>? searcher, long beginRowIndex, int rowsCount)
    {
        ArgumentNullException.ThrowIfNull(conn);
        var sql = searcher.Build().ToSql()!;
        return conn.PagingCommand(sql, beginRowIndex, rowsCount);
    }


    /// <summary>
    /// 执行sql, 返回数据行数
    /// </summary>
    /// <exception cref="ArgumentNullException"></exception>
    public static long ExecuteCount(this IDbConnection conn, FormattableString sql)
    {
        ArgumentNullException.ThrowIfNull(conn);
        ArgumentNullException.ThrowIfNull(sql);
        return conn.Execute(opening =>
        {
            using var command = conn.CreateCommand(sql, null, cmd => cmd.CommandText = conn.GetSqlBuilder().ConvertCountSql(cmd.CommandText));
            opening.Add(command);
            return Convert.ToInt64(command.ExecuteScalar());
        });
    }

    /// <summary>
    /// 执行分页查询
    /// </summary>
    /// <exception cref="ArgumentNullException"></exception>
    public static List<T> ExecutePaging<T>(this IDbConnection conn, FormattableString sql, long beginRowIndex, int rowsCount)
    {
        ArgumentNullException.ThrowIfNull(conn);
        ArgumentNullException.ThrowIfNull(sql);
        if (beginRowIndex < 0)
        {
            throw new ArgumentOutOfRangeException(nameof(beginRowIndex));
        }
        if (rowsCount < 0)
        {
            throw new ArgumentOutOfRangeException(nameof(rowsCount));
        }
        if (rowsCount is 0)
        {
            return [];
        }
        return conn.Execute(opening =>
        {
            using var command = conn.PagingCommand(sql, beginRowIndex, rowsCount);
            opening.Add(command);
            return ExecuteReader(command, reader => reader.ReadList<T>((int)(rowsCount + 1)));
        });
    }


    /// <summary>
    /// 执行分页查询
    /// </summary>
    /// <exception cref="ArgumentNullException"></exception>
    public static List<T> ExecutePaging<T>(this IDbConnection conn, ISqlSearcher<T>? searcher, long beginRowIndex, int rowsCount)
    {
        var sql = searcher.Build().ToSql()!;
        return conn.ExecutePaging<T>(sql, beginRowIndex, rowsCount);
    }

    /// <summary>
    /// 执行分页查询
    /// </summary>
    /// <exception cref="ArgumentNullException"></exception>
    public static List<T> ExecutePaging<T>(this IDbConnection conn, FormattableString sql, long beginRowIndex, int rowsCount, out long totalRowCount)
    {
        var total = 0L;
        var result = conn.Execute(opening =>
        {
            var result = conn.ExecutePaging<T>(sql, beginRowIndex, rowsCount);
            if (result.Count < rowsCount)
            {
                total = beginRowIndex + result.Count;
                return result;
            }
            total = conn.ExecuteCount(sql);
            return result;
        });
        totalRowCount = total;
        return result;
    }

    /// <summary>
    /// 执行分页查询
    /// </summary>
    /// <exception cref="ArgumentNullException"></exception>
    public static List<T> ExecutePaging<T>(this IDbConnection conn, ISqlSearcher<T>? searcher, long beginRowIndex, int rowsCount, out long totalRowCount)
    {
        var sql = searcher.Build().ToSql()!;
        return ExecutePaging<T>(conn, sql, beginRowIndex, rowsCount, out totalRowCount);
    }


    /// <summary>
    /// 执行分页查询
    /// </summary>
    /// <exception cref="ArgumentNullException"></exception>
    public static List<dynamic> ExecutePaging(this IDbConnection conn, FormattableString sql, long beginRowIndex, int rowsCount)
        => conn.ExecutePaging<dynamic>(sql, beginRowIndex, rowsCount);

    /// <summary>
    /// 执行分页查询
    /// </summary>
    /// <exception cref="ArgumentNullException"></exception>
    public static List<dynamic> ExecutePaging(this IDbConnection conn, FormattableString sql, long beginRowIndex, int rowsCount, out long totalRowCount)
        => conn.ExecutePaging<dynamic>(sql, beginRowIndex, rowsCount, out totalRowCount);

}

public static partial class DbConnectionAsyncExtensions
{
    /// <summary>
    /// 获取分页查询 <seealso cref="DbConnection"/> 对象
    /// </summary>
    /// <exception cref="ArgumentNullException"></exception>
    public static Task<DbCommand> PagingCommandAsync(this DbConnection conn, FormattableString sql, long beginRowIndex, int rowsCount, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _PagingCommandAsync(conn, sql, beginRowIndex, rowsCount, cancellationToken);
    }

    /// <summary>
    /// 获取分页查询 <seealso cref="DbConnection"/> 对象
    /// </summary>
    /// <exception cref="ArgumentNullException"></exception>
    public static Task<DbCommand> PagingCommandAsync<T>(this DbConnection conn, ISqlSearcher<T> searcher, long beginRowIndex, int rowsCount, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        var sql = searcher.Build().ToSql()!;
        return _PagingCommandAsync(conn, sql, beginRowIndex, rowsCount, cancellationToken);
    }

    private static Task<DbCommand> _PagingCommandAsync(this DbConnection conn, FormattableString sql, long beginRowIndex, int rowsCount, CancellationToken cancellationToken)
    {
        ArgumentNullException.ThrowIfNull(conn);
        ArgumentNullException.ThrowIfNull(sql);
        if (beginRowIndex < 0)
        {
            throw new ArgumentOutOfRangeException(nameof(beginRowIndex));
        }
        if (rowsCount <= 0)
        {
            throw new ArgumentOutOfRangeException(nameof(rowsCount));
        }

        return conn._CreateCommandAsync(sql, null, cmd => cmd.CommandText = conn.GetSqlBuilder().PagingSql(cmd.CommandText, beginRowIndex, rowsCount), cancellationToken);
    }

    /// <summary>
    /// 执行sql, 返回数据行数
    /// </summary>
    /// <exception cref="ArgumentNullException"></exception>
    public static Task<long> ExecuteCountAsync(this DbConnection conn, FormattableString sql, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecuteCountAsync(conn, sql, cancellationToken);
    }

    /// <summary>
    /// 执行sql, 返回数据行数
    /// </summary>
    /// <exception cref="ArgumentNullException"></exception>
    public static Task<long> ExecuteCountAsync<T>(this DbConnection conn, ISqlSearcher<T>? searcher, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        var sql = searcher.Build().ToSql()!;
        return _ExecuteCountAsync(conn, sql, cancellationToken);
    }
    private static Task<long> _ExecuteCountAsync(this DbConnection conn, FormattableString sql, CancellationToken cancellationToken)
    {
        ArgumentNullException.ThrowIfNull(conn);
        ArgumentNullException.ThrowIfNull(sql);
        return conn._ExecuteAsync(async opening =>
        {
            var command = await conn._CreateCommandAsync(sql, null, x => x.CommandText = conn.GetSqlBuilder().ConvertCountSql(x.CommandText), cancellationToken);
            opening.Add(command);
            return Convert.ToInt64(await command.ExecuteScalarAsync(cancellationToken));
        }, cancellationToken);
    }

    /// <summary>
    /// 执行分页查询
    /// </summary>
    /// <exception cref="ArgumentNullException"></exception>
    public static Task<List<T>> ExecutePagingAsync<T>(this DbConnection conn, FormattableString sql, long beginRowIndex, int rowsCount, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecutePagingAsync<T>(conn, sql, beginRowIndex, rowsCount, cancellationToken);
    }


    /// <summary>
    /// 执行分页查询
    /// </summary>
    /// <exception cref="ArgumentNullException"></exception>
    public static Task<List<T>> ExecutePagingAsync<T>(this DbConnection conn, ISqlSearcher<T>? searcher, long beginRowIndex, int rowsCount, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        var sql = searcher.Build().ToSql()!;
        return _ExecutePagingAsync<T>(conn, sql, beginRowIndex, rowsCount, cancellationToken);
    }


    private static Task<List<T>> _ExecutePagingAsync<T>(this DbConnection conn, FormattableString sql, long beginRowIndex, int rowsCount, CancellationToken cancellationToken)
    {
        ArgumentNullException.ThrowIfNull(conn);
        ArgumentNullException.ThrowIfNull(sql);
        if (beginRowIndex < 0)
        {
            throw new ArgumentOutOfRangeException(nameof(beginRowIndex));
        }
        if (rowsCount < 0)
        {
            throw new ArgumentOutOfRangeException(nameof(rowsCount));
        }
        if (rowsCount is 0)
        {
            return Task.FromResult<List<T>>([]);
        }
        return conn._ExecuteAsync(async opening =>
        {
            var command = await conn._PagingCommandAsync(sql, beginRowIndex, rowsCount, cancellationToken);
            opening.Add(command);
            return await _ExecuteReaderAsync(command, reader => reader._ReadListAsync<T>((int)(rowsCount + 1), cancellationToken), cancellationToken);
        });
    }

    /// <summary>
    /// 执行分页查询
    /// </summary>
    /// <exception cref="ArgumentNullException"></exception>
    public static Task<(List<T> list, long totalRowCount)> ExecutePagingWithTotalAsync<T>(this DbConnection conn, FormattableString sql, long beginRowIndex, int rowsCount, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return _ExecutePagingWithTotalAsync<T>(conn, sql, beginRowIndex, rowsCount, cancellationToken);
    }


    /// <summary>
    /// 执行分页查询
    /// </summary>
    /// <exception cref="ArgumentNullException"></exception>
    public static Task<(List<T> list, long totalRowCount)> ExecutePagingWithTotalAsync<T>(this DbConnection conn, ISqlSearcher<T>? searcher, long beginRowIndex, int rowsCount, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        var sql = searcher.Build().ToSql()!;
        return _ExecutePagingWithTotalAsync<T>(conn, sql, beginRowIndex, rowsCount, cancellationToken);
    }


    private static Task<(List<T> list, long totalRowCount)> _ExecutePagingWithTotalAsync<T>(this DbConnection conn, FormattableString sql, long beginRowIndex, int rowsCount, CancellationToken cancellationToken)
    {
        return conn._ExecuteAsync(async opening =>
        {
            var result = await conn._ExecutePagingAsync<T>(sql, beginRowIndex, rowsCount, cancellationToken);
            if (result.Count < rowsCount)
            {
                return (result, beginRowIndex + result.Count);
            }
            var totalRowCount = await conn._ExecuteCountAsync(sql, cancellationToken);
            return (result, totalRowCount);
        });
    }

    /// <summary>
    /// 执行分页查询
    /// </summary>
    /// <exception cref="ArgumentNullException"></exception>
    public static Task<List<dynamic>> ExecutePagingAsync(this DbConnection conn, FormattableString sql, long beginRowIndex, int rowsCount, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return conn._ExecutePagingAsync<dynamic>(sql, beginRowIndex, rowsCount, cancellationToken);
    }

    /// <summary>
    /// 执行分页查询
    /// </summary>
    /// <exception cref="ArgumentNullException"></exception>
    public static Task<(List<dynamic> list, long totalRowCount)> ExecutePagingWithTotalAsync(this DbConnection conn, FormattableString sql, long beginRowIndex, int rowsCount, CancellationToken cancellationToken = default)
    {
        cancellationToken = DbInterceptors.GetCancellationToken(cancellationToken);
        return conn._ExecutePagingWithTotalAsync<dynamic>(sql, beginRowIndex, rowsCount, cancellationToken);
    }
}
